package databaseDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sun.org.apache.bcel.internal.generic.LDIV;

import bean.MenuBean;
import bean.RoleBean;
import bean.UserBean;

public class Menu2Dao {

	public List<MenuBean> queryMenu() throws ClassNotFoundException{
		List<MenuBean> listBean = new ArrayList<MenuBean>();
		Connection con = DataAccess.getConnection();
		String sql = "select * from menu2 where flag != '0'";

		PreparedStatement pst = null;
		ResultSet rs = null;
		MenuBean mb = null;
		
		try {
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();

			while (rs.next()) {
				mb = new MenuBean();
				mb.setMenuID(rs.getString("id"));
				mb.setFunctionName(rs.getString("functionName"));
				mb.setUrl(rs.getString("url"));
				mb.setForeID(rs.getString("foreID"));
				listBean.add(mb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(pst!=null){
					pst.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return listBean;
	}
	
	
	//public  void 
	
	
	public List<MenuBean> queryMenuByRoleID(String RoleID) throws ClassNotFoundException{
		List<MenuBean> listBean = new ArrayList<MenuBean>();
		Connection con = DataAccess.getConnection();
		String sql = "select DISTINCT m1.* from menu2 as m1,(select * from menu2 m3 where m3.id in"
				+ " (select menuID from roleright where roleID='"+RoleID+"')) as m2 "
				+ "where m1.id in (m2.id) or m1.id in (m2.foreID) or m1.foreID in (m2.id) ORDER BY m1.id";

		PreparedStatement pst = null;
		ResultSet rs = null;
		MenuBean mb = null;
		
		try {
			pst = con.prepareStatement(sql);
			
			rs = pst.executeQuery();

			while (rs.next()) {
				mb = new MenuBean();
				mb.setMenuID(rs.getString("id"));
				mb.setFunctionName(rs.getString("functionName"));
				mb.setUrl(rs.getString("url"));
				mb.setForeID(rs.getString("foreID"));
				listBean.add(mb);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(pst!=null){
					pst.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return listBean;
	}
	
	
public MenuBean queryRoleByRoleIDInMenu2(String roleID) throws Exception {
		
		Connection con = DataAccess.getConnection();
		String sql = "select * from roleright rr,menu2 m2 where rr.menuID=m2.id and rr.roleID='"+roleID+"'";

		ResultSet rs = null;
		RoleBean rb = null;
		Statement stmt = null;
		MenuBean mb =null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				mb = new MenuBean();
				mb.setMenuID(rs.getString("id"));
				mb.setFunctionName(rs.getString("functionName"));
				mb.setUrl(rs.getString("url"));
				mb.setForeID(rs.getString("foreID"));
				
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return mb;
	}
	
	// 获取用户权限列表 通过权限
	public List<String> getMenuIDByroleID(String roleID) throws ClassNotFoundException{
		List<String> lsMenuID = new ArrayList<String>();
		
		Connection con = DataAccess.getConnection();
		String sql = "select * from roleright where roleID='"+roleID+"'";
		
		ResultSet rs = null;
		RoleBean rb = null;
		Statement stmt = null;
		MenuBean mb =null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				String s = rs.getString("menuID");
				lsMenuID.add(s);
				System.out.println("s:"+s);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		
		
		
		System.out.println("roleID"+roleID);
		return lsMenuID;
		
	}


	public void insertToRoleright(String roleid, String menuid) throws ClassNotFoundException {
		Connection con = DataAccess.getConnection();  // 获取连接
		String sql = "insert into roleright values(?,?)";  // 编写sql语句
		PreparedStatement pst = null; // 创建预处理语句
		try{
			pst = con.prepareStatement(sql);  // 拿到预处理
			pst.setString(1, roleid);
			pst.setString(2, menuid);
			
			
			pst.executeUpdate(); // 提交
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(pst!=null){
					pst.close();
					
				}
				if(con!=null){
					con.close();
					
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return ;
		
	}


	public void deleteFromRolerigt(String roleid, String menuid) throws Exception {
		// TODO 自动生成的方法存根
		Connection con = DataAccess.getConnection();
		
		String sql = "delete from roleright where roleID=? and menuID=?";
		PreparedStatement pst = null;
		
		
		pst = con.prepareStatement(sql);
		pst.setString(1, roleid);
		pst.setString(2, menuid);
		pst.executeUpdate();
		
			try{
				
				if(pst!=null){
					pst.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
	}
	
}
